Database Tutorials JSON Data Query করা (Operators এবং Functions) গাইড ও নোট

PostgreSQL একটি শক্তিশালী ডেটাবেস সিস্টেম যা JSON এবং JSONB ডেটা টাইপ সাপোর্ট করে। JSON ডেটা ফরম্যাট বেশিরভাগ ওয়েব অ্যাপ্লিকেশন ও API ইন্টিগ্রেশনে ব্যবহৃত হয়, এবং PostgreSQL এর মধ্যে JSON ডেটা কার্যকরভাবে অনুসন্ধান এবং পরিচালনা করার জন্য বিভিন্ন Operators এবং Functions সরবরাহ করে।

এই টিউটোরিয়ালে PostgreSQL তে JSON ডেটা Query করার জন্য ব্যবহৃত সাধারণ Operators এবং Functions সম্পর্কে বিস্তারিত আলোচনা করা হবে।

1. JSON Data Types in PostgreSQL

PostgreSQL JSON ডেটা স্টোরেজের জন্য দুটি ডেটা টাইপ সাপোর্ট করে:

• JSON: এটি স্টোর করে মূল JSON ডেটা ফরম্যাটে (টেক্সট হিসাবে), কিন্তু পারফরম্যান্স কিছুটা কম হতে পারে।
• JSONB: এটি একটি বাইনারি ফরম্যাটে JSON ডেটা স্টোর করে এবং এর পারফরম্যান্স সাধারণত আরও ভালো হয়, কারণ এটি ইনডেক্সিং সাপোর্ট করে এবং দ্রুত পার্সিং করতে সক্ষম।

JSON এবং JSONB উভয়ের সাথে বিভিন্ন অপারেটর এবং ফাংশন ব্যবহার করা যেতে পারে।

2. JSON Querying Operators

PostgreSQL JSON ডেটার উপর কাজ করতে বিভিন্ন অপারেটর ব্যবহার করা যায়, যার মাধ্যমে আপনি JSON ডেটার ভিতরের অংশে অ্যাক্সেস করতে পারেন এবং তাদের উপর কাজ করতে পারেন।

a. -> Operator

-> অপারেটরটি JSON ডেটার একটি কী এর মান ফিরিয়ে দেয় (যদি তা একটি অবজেক্ট বা অ্যারে হয়)।

• JSONB বা JSON Object থেকে একটি কী এর মান:

  SELECT data->'name' FROM users;
  

  এখানে data JSON বা JSONB কলাম হতে name কী এর মান বের করা হয়েছে।

b. ->> Operator

->> অপারেটরটি JSON ডেটার একটি কী এর মানকে টেক্সট হিসাবে ফিরিয়ে দেয়।

• JSONB বা JSON Object থেকে একটি কী এর মান টেক্সট আকারে:

  SELECT data->>'name' FROM users;
  

  এখানে, name কী এর মান টেক্সট আকারে ফিরে আসবে, যেমন "John"।

c. #>> Operator

#>> অপারেটরটি JSONB অথবা JSON ডেটার একটি নেস্টেড কী এর মান টেক্সট হিসেবে ফিরিয়ে দেয়। এটি JSON অবজেক্টের গভীরে একটি কী পর্যন্ত অ্যাক্সেস করতে ব্যবহৃত হয়।

• Nested JSON এর মান টেক্সট আকারে:

  SELECT data#>>'{address, city}' FROM users;
  

  এখানে address অবজেক্টের ভিতরে থাকা city কী এর মান টেক্সট হিসেবে বের করা হচ্ছে।

d. @> Operator

@> অপারেটরটি চেক করে যে একটি JSONB ডেটা অন্য একটি JSONB ডেটাকে অন্তর্ভুক্ত করছে কি না। এটি JSONB containment অপারেটর নামে পরিচিত।

• JSONB ডেটা containment চেক:

  SELECT * FROM users WHERE data @> '{"name": "John"}';
  

  এই কুয়েরি data কলামে name কী এর মান "John" রয়েছে এমন রেকর্ডগুলি ফিরিয়ে আনবে।

e. <@ Operator

<@ অপারেটরটি চেক করে যে একটি JSONB ডেটা আরেকটি JSONB ডেটার মধ্যে অন্তর্ভুক্ত কিনা।

• JSONB containment উল্টো চেক:

  SELECT * FROM users WHERE '{"name": "John"}' <@ data;
  

f. || Operator

|| অপারেটরটি দুটি JSONB অবজেক্ট একত্রিত করতে ব্যবহৃত হয়। এটি দুটি JSONB অবজেক্টকে একসাথে যুক্ত করে।

• JSONB objects combine:

  SELECT data || '{"city": "New York"}' FROM users;
  

g. - Operator

- অপারেটরটি JSONB থেকে একটি নির্দিষ্ট কী মুছে ফেলার জন্য ব্যবহৃত হয়।

• JSONB থেকে কী মুছে ফেলা:

  SELECT data - 'name' FROM users;
  

3. JSON Functions

PostgreSQL JSON ডেটার সাথে আরও কার্যকরভাবে কাজ করতে একটি সিরিজ ফাংশন সরবরাহ করে। এখানে কিছু গুরুত্বপূর্ণ JSON ফাংশন আলোচনা করা হলো:

a. jsonb_array_elements()

এই ফাংশনটি JSONB অ্যারে থেকে প্রতিটি উপাদানকে আলাদাভাবে বের করে। এটি একটি সেট-ভ্যালু ফাংশন (set-returning function) যা একাধিক রেকর্ড ফেরত দেয়।

• JSONB অ্যারে উপাদান বের করা:

  SELECT jsonb_array_elements(data->'tags') FROM users;
  

  এখানে tags অ্যারে থেকে প্রতিটি উপাদান আলাদাভাবে বের করা হচ্ছে।

b. jsonb_each()

এই ফাংশনটি JSONB অবজেক্টের প্রতিটি কী এবং মানকে একটি সেট রিটার্ন করে। এটি JSONB অবজেক্টের কীগুলি এবং তাদের মান বের করতে ব্যবহৃত হয়।

• JSONB অবজেক্টের প্রতিটি কী-মান পেয়ার বের করা:

  SELECT * FROM jsonb_each(data);
  

c. jsonb_extract_path()

এই ফাংশনটি JSONB ডেটার একটি নির্দিষ্ট পথ থেকে মান বের করে। এটি নেস্টেড JSON ডেটা থেকে মান বের করার জন্য ব্যবহৃত হয়।

• নেস্টেড JSONB থেকে মান বের করা:

  SELECT jsonb_extract_path(data, 'address', 'city') FROM users;
  

d. jsonb_set()

এই ফাংশনটি JSONB ডেটার একটি নির্দিষ্ট কী এর মান আপডেট করতে ব্যবহৃত হয়।

• JSONB এর মান আপডেট করা:

  SELECT jsonb_set(data, '{address, city}', '"San Francisco"') FROM users;
  

e. jsonb_to_record()

এই ফাংশনটি JSONB ডেটা থেকে একটি রেকর্ড তৈরি করতে ব্যবহৃত হয়, যা ডেটা রূপান্তর করতে সহায়তা করে।

• JSONB থেকে রেকর্ড তৈরি করা:

  SELECT * FROM jsonb_to_record(data) AS x(name text, age int);
  

4. JSON Query Examples

a. JSON Data থেকে Filter করা

ধরা যাক, আপনার users টেবিলে JSON ডেটা রয়েছে এবং আপনি সেই ডেটার নির্দিষ্ট মান অনুসারে রেকর্ড বের করতে চান:

SELECT * FROM users WHERE data->>'name' = 'John';

এটি name কলামের মান "John" এর সাথে মেলানো রেকর্ড ফিরিয়ে আনবে।

b. JSONB Data Filtering

যদি JSONB ডেটা থাকে এবং আপনি JSONB কন্টেইনমেন্ট অপারেটর ব্যবহার করতে চান, তাহলে আপনি এমন কুয়েরি করতে পারেন:

SELECT * FROM users WHERE data @> '{"address": {"city": "New York"}}';

এটি data কলামে address অবজেক্টের মধ্যে city "New York" থাকা রেকর্ড ফিরিয়ে আনবে।

c. JSONB Field Update

JSONB ডেটায় একটি নির্দিষ্ট মান আপডেট করতে:

UPDATE users SET data = jsonb_set(data, '{address, city}', '"Chicago"') WHERE id = 1;

এটি id 1 এর ব্যবহারকারীর address.city মান পরিবর্তন করবে "Chicago" তে।

5. Performance Considerations

1. Indexing: JSONB ডেটাতে দ্রুত অনুসন্ধান করতে GIN (Generalized Inverted Index) ইনডেক্স ব্যবহার করা যেতে পারে। এটি JSONB ডেটার সঠিক ফিল্টারিং এবং অনুসন্ধান দ্রুত করতে সহায়ক।

   CREATE INDEX idx_gin_data ON users USING gin (data);
   

2. Use JSONB for Performance: JSONB দ্রুত পার্সিং এবং ইনডেক্সিং সাপোর্ট করে, তাই যদি পারফরম্যান্স গুরুত্বপূর্ণ হয়, তবে JSONB ব্যবহার করা ভালো।

Conclusion

PostgreSQL এর JSON এবং JSONB ফিচারগুলি ডেটাবেসে JSON ডেটার সাথে কাজ করার জন্য অনেক শক্তিশালী অপারেটর এবং ফাংশন সরবরাহ করে। Operators যেমন ->, ->>, @>, এবং Functions যেমন jsonb_array_elements(), jsonb_each() ইত্যাদি ডেটার গভীরে প্রবেশ এবং তার উপর কার্যকরভাবে কাজ করার জন্য ব্যবহৃত হয়। JSON ডেটার ব্যবহার PostgreSQL তে ব্যাপক এবং সহজে সাপোর্ট করা হয়, বিশেষত যখন আপনাকে ওয়েব অ্যাপ্লিকেশন বা API

এর সাথে ইন্টিগ্রেশন করতে হয়।